package com.zr.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.model.SalaryModel;
import com.zr.util.JDBCUtil;
/**
 *  获取所有员工工资
 * @author Administrator
 *
 */
public class SalaryDao {
    public List<SalaryModel> getAllSalary(int start,int rows){
    	//定义个员工工资的集合
		List<SalaryModel> salarys= new ArrayList<SalaryModel>();
    	String sql="select * from salary limit ?,?";
    	Connection con= JDBCUtil.getDBConnection();
    	try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setInt(1, start);
		    pst.setInt(2, rows);
			ResultSet rs=pst.executeQuery();
		    while(rs.next()){
		    	//实例化员工工资并添加到集合中
		    	SalaryModel salartmodel= new SalaryModel();
		    	salartmodel.setSid(rs.getInt("sid"));
		    	salartmodel.setWid(rs.getInt("wid"));
		    	salartmodel.setWname(rs.getString("wname"));
		    	salartmodel.setDid(rs.getInt("did"));
		    	salartmodel.setMonth(rs.getInt("month"));
		    	salartmodel.setSalary(rs.getString("salary"));
		    	salartmodel.setPremium(rs.getString("premium"));
		    	salartmodel.setDeduct(rs.getString("deduct"));
		    	salarys.add(salartmodel);
		    }
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	finally {
			JDBCUtil.closedConnection(con);
		}
    	return salarys;
    }
     /**
      * 得到所有员工工资的数据
      * @return
      */
   public int getAllSalarycount(){
        	int scount=0;
        	String sql="select count(*) scount from salary";
        	Connection con= JDBCUtil.getDBConnection();
    		try {
    			PreparedStatement pst = con.prepareStatement(sql);
    		    ResultSet rs= pst.executeQuery(); 
    		    if(rs.next()){
    		    	scount=rs.getInt("scount");
    		    }
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}finally {
    			JDBCUtil.closedConnection(con);
    		}
    		return scount;
        }
 /**
  *   添加工资信息的功能
  * @param wid
  * @param wname
  * @param did
  * @param month
  * @param salary
  * @param premium
  * @param deduct
  * @return
  */
    public int addSalary(int wid,String wname,int did,int month,String salary,String premium,String deduct){
    	int temp=0;
    	String sql="insert into salary (wid,wname,did,month,salary,premium,deduct) values(?,?,?,?,?,?,?);";
		Connection con= JDBCUtil.getDBConnection();
    	try {
			PreparedStatement pst= con.prepareStatement(sql);
		    pst.setInt(1, wid);
    	    pst.setString(2, wname);
    	    pst.setInt(3, did);
    	    pst.setInt(4, month);
    	    pst.setString(5, salary);
    	    pst.setString(6, premium);
    	    pst.setString(7, deduct);
    	    int i=pst.executeUpdate();
    	    if(i>0){
    	    	temp=1;
    	    }
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return temp;
    }
 /**
  * 验证是否有该员工存在
  * @param wid
  * @return
  */
    public boolean validataWname(int wid){
    	
    	boolean flag=false;
    	String sql="select wid from worker where wid=?";
    	Connection con=JDBCUtil.getDBConnection();
    	try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setInt(1, wid);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				flag=true;
			}
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return flag;
    }    
 /**
  *   查询一个员工所有的工资数据 
  * @param start
  * @param rows
  * @return
  */
    public List<SalaryModel> selectOneWorkerSalary(int wid,int start,int rows){
	  List<SalaryModel> oneworkersalarys= new ArrayList<SalaryModel>();
	  String sql="select * from salary where wid=? limit ?,?";
		Connection con= JDBCUtil.getDBConnection();
    	try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setInt(1, wid);
			pst.setInt(2, start);
			pst.setInt(3, rows);
			ResultSet rs=pst.executeQuery();
		    while(rs.next()){
		    	//实例化员工工资并添加到集合中
		    	SalaryModel salartmodel= new SalaryModel();
		    	salartmodel.setWid(rs.getInt("wid"));
		    	salartmodel.setWname(rs.getString("wname"));
		    	salartmodel.setDid(rs.getInt("did"));
		    	salartmodel.setMonth(rs.getInt("month"));
		    	salartmodel.setSalary(rs.getString("salary"));
		    	salartmodel.setPremium(rs.getString("premium"));
		    	salartmodel.setDeduct(rs.getString("deduct"));
		    	oneworkersalarys.add(salartmodel);
		    }
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	finally {
			JDBCUtil.closedConnection(con);
		}
	  return oneworkersalarys;
  } 
    public int SelectOneSalarycount(int wid){
    	int scount=0;
    	String sql="select count(*) scount from salary where wid=?";
    	Connection con= JDBCUtil.getDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql);
		    pst.setInt(1, wid);
			ResultSet rs= pst.executeQuery(); 
		    if(rs.next()){
		    	scount=rs.getInt("scount");
		    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
		return scount;
    }
    /**
     * 修改工资
     * @param sid
     * @param salary
     * @param premium
     * @param deduct
     * @return
     */
    public int updateSalary(int sid,String salary,String premium,String deduct){
    	 System.out.println(salary);
    	int temp=0;
    	 String sql="update salary set salary=?,premium=?,deduct=? where sid=?";
    	 Connection con=JDBCUtil.getDBConnection();
    	 try {
			PreparedStatement pst= con.prepareStatement(sql);
		    pst.setString(1, salary);
		    pst.setString(2, premium);
		    pst.setString(3, deduct);
    	    pst.setInt(4, sid);
    	    int i =pst.executeUpdate();
    	    if(i>0){
    	    	temp=1;
    	    }
    	 } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
		return temp;
    }
}
